clear all
set more off, perm
set varabbrev off
/* ********************************************************************* */
* Get IBES forecast data
import sas $Data/IBES/statsumu_epsus.sas7bdat, clear case(lower)
rename *, lower
compress
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 
* keep forecasts for quarterly EPS
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")
destring fpi, replace
keep if (fpi>=6 & fpi<=9)

keep statpers ticker fpi medest fpedats

* interpolate the data
egen id=group(ticker statpers)
gen fpdatem=mofd(fpedats)
format fpdatem %tm
* **** 
drop fpedats fpi
tsset id fpdatem
tsfill
* ****
sort id fpdatem 
replace statpers=L.statpers if missing(statpers) & id==id[_n-1] 
replace ticker=ticker[_n-1] if missing(ticker)   & id==id[_n-1]

by id: ipolate medest fpdatem, gen(imedest)
drop medest

gen fpi=(fpdatem-mofd(statpers))
keep if fpi>0  

by id: egen max=max(fpi)

generate QTR=1 if fpi==3
replace  QTR=2 if fpi==6
replace  QTR=3 if fpi==9
replace  QTR=4 if fpi==12 | (fpi==max & max>9 & max<12) 
keep if QTR<.
drop max

keep ticker statpers fpi imedest QTR
rename imedest medestQTR
* ****
reshape wide medestQTR fpi, i(ticker statpers) j(QTR)
forvalues x=2(1)4 {
	local j=`x'-1
	replace fpi`x'=fpi`j'+3 if fpi`x'==.
}
gen fpedatsQTR1=statpers+fpi1
gen fpedatsQTR2=statpers+fpi2
gen fpedatsQTR3=statpers+fpi3
gen fpedatsQTR4=statpers+fpi4

format fpedats* %td
* ****
drop fpi*
order *, sequential
order ticker statpers
* ****
save $Work/read_ibes_qtly.dta, replace
/* ********************************************************************* */
!find $Temp -type f -delete

